set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
insert overwrite table jms_dm.dm_waybill_collect_intime_plan_agg
select
      plan_arr_date              --规划日期
     ,receive_city_id            --收件城市id
     ,receive_city               --收件城市
     ,region_code                --收件管理大区编码
     ,region_name                --收件管理大区名称
     ,receive_proxy_code         --收件代理区编码
     ,receive_proxy              --收件代理区名称
     ,receive_franchisee_code    --收件加盟商编码
     ,receive_franchisee         --收件加盟商名称
     ,receive_network_code       --收件网点编码
     ,receive_network_name       --收件网点名称
     ,order_source               --订单来源订单来源1紫金山2逍遥峰3桃花岛4其他
     ,goods_type_code            --物品类型编码
     ,goods_type_name            --物品类型名称
     ,is_interrupted             --是否截单0否1是2其他
     ,three_code_num             --三段码问题:无目的网点
     ,not_route                  --规划问题:无路由
     ,plan_err_num               --规划问题
     ,other                      --其他问题:自收自派
     ,wrong_dispatch             --无路由:错发
     ,collect_code               --交件集散编码
     ,collect_name               --交件集散名称
     ,center_code                --交件转运中心编码
     ,center_name                --交件转运中心名称
     ,null as should_hand_in_num --应交件票数
     ,null as in_time_num        --准点交件票数
     ,null as in_time_rate       --交件准点率
     ,null as not_in_time_num    --不准点交件票数
     ,null as network_resp_num   --交件网点责任票数
     ,null as collect_resp_num   --交件集散责任票数
     ,should_hand_over_num       --应交接票数
     ,hand_over_in_time_num      --准点交接票数
     ,should_hand_over_num - hand_over_in_time_num  as hand_over_not_in_time_num --不准点交接票数
     ,hand_over_network_resp_num --交接网点责任票数
     ,hand_over_collect_resp_num --交接集散责任票数
     ,same_city_num              --同城件票数
     ,unarrival_num              --未到交接时间票数
     ,round(hand_over_in_time_num / should_hand_over_num,2) as hand_over_in_time_rate --交接准点率
     ,not_intime_cnt
     ,express_type_code        as express_type_code       --产品类型编码  (20221103新增)
     ,express_type_name        as express_type_name       --产品类型名称  (20221103新增)
     ,yiqin_late_cnt           as yiqin_late_cnt          --疫情滞留量
     ,route_late_cnt           as route_late_cnt          --路由晚点量
     ,center_send_timely_cnt   as center_send_timely_cnt  --交件晚点及时发出量
     ,center_send_late_cnt     as center_send_late_cnt    --交件晚点未及时发出量
     ,first_nodal_agent_code   as first_nodal_agent_code  --实际交件集散点代理区编码
     ,first_nodal_agent_name   as first_nodal_agent_name  --实际交件集散点代理区名称
     ,first_nodal_fran_code    as first_nodal_fran_code   --实际交件集散点加盟商编码
     ,first_nodal_fran_name    as first_nodal_fran_name   --实际交件集散点加盟商名称
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
     ,white_list_type
     ,plan_arr_date as dt
from(
    select
          plan_arr_date            --规划日期
         ,receive_city_id          --收件城市id
         ,receive_city             --收件城市
         ,region_code              --收件管理大区编码
         ,region_name              --收件管理大区名称
         ,receive_proxy_code       --收件代理区编码
         ,receive_proxy            --收件代理区名称
         ,receive_franchisee_code  --收件加盟商编码
         ,receive_franchisee       --收件加盟商名称
         ,receive_network_code     --收件网点编码
         ,receive_network_name     --收件网点名称
         ,order_source             --订单来源订单来源1紫金山2逍遥峰3桃花岛4其他
         ,goods_type_code          --物品类型编码
         ,goods_type_name          --物品类型名称
         ,is_interrupted           --是否截单0否1是2其他
         ,actual_collect_code      as collect_code       --交件集散编码
         ,actual_collect_name      as collect_name       --交件集散名称
         ,actual_center_code       as center_code        --交件转运中心编码
         ,actual_center_name       as center_name        --交件转运中心名称
         ,express_type_code        as express_type_code  --产品类型编码(20221103新增)
         ,express_type_name        as express_type_name  --产品类型名称(20221103新增)
         ,first_nodal_agent_code   as first_nodal_agent_code  --实际交件集散点代理区编码
         ,first_nodal_agent_name   as first_nodal_agent_name  --实际交件集散点代理区名称
         ,first_nodal_fran_code    as first_nodal_fran_code   --实际交件集散点加盟商编码
         ,first_nodal_fran_name    as first_nodal_fran_name   --实际交件集散点加盟商名称
         ,count(case when type = 1 then waybill end) as three_code_num --三段码问题:无目的网点
         ,count(case when type = 2 then waybill end) as not_route      --规划问题:无路由
         ,count(case when type = 2 then waybill end) as plan_err_num   --规划问题
         ,count(case when type = 3 then waybill end) as other          --其他问题:自收自派
         ,count(case when type = 5 then waybill end) as wrong_dispatch --无路由:错发
         ,count(case when type = 4 then waybill end) as same_city_num  --同城件票数
         ,count(case when type = 6 then waybill end) as unarrival_num  --未到交接时间票数
         ,count(case when is_over is not null and type = 0  then waybill end) as should_hand_over_num --应交接票数
         ,count(case when is_over = 1 and type = 0 then waybill end) as hand_over_in_time_num --准点交接票数
         ,count(case when is_over = 0 and type = 0 then waybill end) as hand_over_network_resp_num --交接网点责任票数
         ,count(case when is_over = 2 and type = 0 then waybill end) as hand_over_collect_resp_num --交接集散责任票数
         ,count(case when is_over is not null and type = 0 and is_intime = 0 then waybill end) as not_intime_cnt --时效不准点数
         ,count(case when if_yiqin_late         = 1 then waybill end) as yiqin_late_cnt          --疫情滞留量
         ,count(case when if_route_late         = 1 then waybill end) as route_late_cnt          --路由晚点量
         ,count(case when if_center_send_timely = 1 then waybill end) as center_send_timely_cnt  --交件晚点及时发出量
         ,count(case when if_center_send_timely = 0 then waybill end) as center_send_late_cnt    --交件晚点未及时发出量
        ,order_mark_en                    -- 订单标签英文
        ,order_mark_zh                    -- 订单标签中文
        ,white_list_type
    from jms_dm.dm_waybill_collect_intime_plan
    where dt = '{{ execution_date | cst_ds }}'
    group by plan_arr_date
            ,receive_city_id
            ,receive_city
            ,region_code
            ,region_name
            ,receive_proxy_code
            ,receive_proxy
            ,receive_franchisee_code
            ,receive_franchisee
            ,receive_network_code
            ,receive_network_name
            ,order_source
            ,goods_type_code
            ,goods_type_name
            ,is_interrupted
            ,actual_collect_code
            ,actual_collect_name
            ,actual_center_code
            ,actual_center_name
            ,express_type_code       --产品类型编码  (20221103新增)
            ,express_type_name       --产品类型名称  (20221103新增)
            ,first_nodal_agent_code  --实际交件集散点代理区编码
            ,first_nodal_agent_name  --实际交件集散点代理区名称
            ,first_nodal_fran_code   --实际交件集散点加盟商编码
            ,first_nodal_fran_name   --实际交件集散点加盟商名称
            ,order_mark_en                    -- 订单标签英文
            ,order_mark_zh                    -- 订单标签中文
            ,white_list_type
) a
distribute by dt
;

